
/*********************
 **  CreateTime: 2006-7-25 
 **********************/

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

using EntLibEC.Cmn;
using EntLibEC.Info.PIM;

namespace EntLibEC.Dac.PIM
{
    /// <summary>
    /// Contains the the data access methods for PropertyGroupDA.
    /// </summary>
    public abstract class PropertyGroupDA
    {
        #region Sql Script

        private const string SQL_GET_ALL_PROPERTYGROUP
            = @"SELECT a.SysNo,a.GroupDescription,a.LastEditUserSysNo,a.LastEditTime,b.UserName as LastEditUsername 
				FROM PIM_PropertyGroup a (NOLOCK)
                LEFT JOIN Ent_Sys_User b (NOLOCK) ON a.LastEditUserSysNo = b.SysNo
                ORDER BY a.GroupDescription";

        public const string SQL_GET_ALL_PROPERTYGROUP_FOR_CACHE
            = "SELECT SysNo, GroupDescription FROM dbo.PIM_PropertyGroup ORDER BY GroupDescription";

        private const string SQL_GET_PROPERTYGROUP_SYSNO 
            = @"SELECT TOP 1 SysNo FROM PIM_PropertyGroup (NOLOCK) 
                WHERE GroupDescription = @GroupDescription";

        private const string SQL_INSERT_PROPERTYGROUP = "INSERT INTO PIM_PropertyGroup (GroupDescription,LastEditUserSysNo,LastEditTime) VALUES (@GroupDescription,@LastEditUserSysNo,GETDATE()) select @@Identity";
        private const string SQL_DELETE_PROPERTYGROUP = "DELETE FROM PIM_PropertyGroup WHERE SysNo = @SysNo";
        #endregion

        protected static PropertyGroupInfo Map(DataRow row)
        {
            PropertyGroupInfo info = new PropertyGroupInfo();

            info.SysNo = Convert.ToInt32(row["SysNo"]);
            info.GroupDescription = row["GroupDescription"].ToString();
            info.LastEditUserSysNo = Convert.ToInt32(row["LastEditUserSysNo"]);
            info.LastEditTime = Convert.ToDateTime(row["LastEditTime"]);

            info.LastEditUsername = row["LastEditUsername"].ToString();

            return info;
        }

        public static List<PropertyGroupInfo> GetAllPropertyGroups()
        {
            DataTable dt = DataAccessFacade.GetDataTable(SQL_GET_ALL_PROPERTYGROUP);
            if (dt == null || dt.Rows.Count ==0)
                return null;

            List<PropertyGroupInfo> list = new List<PropertyGroupInfo>();
            foreach (DataRow row in dt.Rows)
                list.Add(Map(row));

            return list;
        }

        public static int GetGroupSysNo(string groupName)
        {
            SqlParameter[] parms = new SqlParameter[1];

            parms[0] = new SqlParameter("@GroupDescription", SqlDbType.NVarChar, 50);
            parms[0].Value = groupName;

            object obj = DataAccessFacade.ExecScalarSqlCommand(SQL_GET_PROPERTYGROUP_SYSNO, parms);
            return Convert.ToInt32(obj);
        }

        public static int InsertPropertyGroup(PropertyGroupInfo info)
        {
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@SysNo", SqlDbType.Int),
                new SqlParameter("@GroupDescription", SqlDbType.NVarChar, 50),
                new SqlParameter("@LastEditUserSysNo", SqlDbType.Int)
            };

            parms[0].Value = info.SysNo;
            parms[1].Value = info.GroupDescription;
            parms[2].Value = info.LastEditUserSysNo;

            object obj = DataAccessFacade.ExecScalarSqlCommand(SQL_INSERT_PROPERTYGROUP, parms);
            return Convert.ToInt32(obj);
        }

        public static void DeletePropertyGroup(int SysNo)
        {
            SqlParameter[] parms = new SqlParameter[1];

            parms[0] = new SqlParameter("@SysNo", SqlDbType.Int);
            parms[0].Value = SysNo;

            DataAccessFacade.ExecSqlCommand(SQL_DELETE_PROPERTYGROUP, parms);
        }
    }
}
